/*
 * CDataStore.cpp
 *
 *  Created on: 2018年5月11日
 *      Author: zhengboyuan
 */

#include "CDataStore.h"
#include "TStringUtil.h"
#include "TStringJoiner.h"
#include "TSafeStr.h"
#include "JsonUtil.h"

static bool isEmpty(const char* str)
{
	return (str == NULL || strlen(str) == 0);
}

bool toObject(sqlite3_stmt* stmt, Json::Value& record)
{
	int count = sqlite3_column_count(stmt);
	for (int i = 0; i < count; i ++)
	{
		std::string name = sqlite3_column_name(stmt, i);
		int type = sqlite3_column_type(stmt, i);
		if (type == SQLITE_INTEGER)
		{
			int value = sqlite3_column_int(stmt, i);
			record[name] = value;
		}
		else if (type == SQLITE_FLOAT)
		{
			double value = sqlite3_column_double(stmt, i);
			record[name] = value;
		}
		else if (type == SQLITE_TEXT)
		{
			const char* value = (const char*)sqlite3_column_text(stmt, i);
			record[name] = value;
		}
		else if (type == SQLITE_BLOB)
		{
			// pass
		}
		else if (type == SQLITE_NULL)
		{
			record[name] = Json::nullValue;
		}
	}
    return true;
}


bool toObject(sqlite3_stmt* stmt, EventRecord& record)
{
    int col = 0;

    record.id = sqlite3_column_int(stmt, col ++);
    record.type = sqlite3_column_int(stmt, col ++);
    comn::copyStr(record.company,(const char*)sqlite3_column_text(stmt, col ++));
    comn::copyStr(record.station,(const char*)sqlite3_column_text(stmt, col ++));
    record.num = sqlite3_column_int(stmt, col ++);
    record.capacity = sqlite3_column_int(stmt, col ++);
    record.peoples = sqlite3_column_int(stmt, col ++);
    record.status = sqlite3_column_int(stmt, col ++);

	comn::copyStr(record.memo, (const char*)sqlite3_column_text(stmt, col++));
    comn::copyStr(record.filepath,(const char*)sqlite3_column_text(stmt, col ++));

    record.created = sqlite3_column_int64(stmt, col ++);

    return true;
}

bool toObject(sqlite3_stmt* stmt, ImageRecord& record)
{
	int col = 0;

	record.id = sqlite3_column_int(stmt, col ++);
	comn::copyStr(record.company,(const char*)sqlite3_column_text(stmt, col ++));
	comn::copyStr(record.station,(const char*)sqlite3_column_text(stmt, col ++));
	record.num = sqlite3_column_int(stmt, col ++);

	comn::copyStr(record.filepath,(const char*)sqlite3_column_text(stmt, col ++));
	record.created = sqlite3_column_int64(stmt, col ++);

	return true;
}


std::string CDataStore::toJsonString(int offset, int limit, int total, JsonList& items)
{
	Json::Value json;
	json["offset"] = offset;
	json["limit"] = limit;
	json["total"] = total;

	Json::Value subjson(Json::arrayValue);
	for (size_t i = 0; i < items.size(); i ++)
	{
		subjson.append(items[i]);
	}

	json["items"] = subjson;
	return json.toStyledString();
}

std::string makeUpdate(const std::string& table, const Json::Value& json)
{
	std::ostringstream ss;
	ss << "UPDATE ";
	ss << table;
	ss << " set ";
	ss << JsonUtil::join(json, "=", ",", '`', "", "", JsonUtil::kIgnoreNull | JsonUtil::kQuoteString);
	return ss.str();
}

std::string makeInsert(const std::string& table, const std::string& verb, const Json::Value& json)
{
	std::ostringstream ss;
	ss << verb;
	ss << " ";
	ss << table;
	ss << " ";
	ss << JsonUtil::joinKey(json, ",", '`', "(", ")", JsonUtil::kIgnoreNull | JsonUtil::kQuoteString);
	ss << " ";
	ss << JsonUtil::joinValue(json, ",", "values(", ")", JsonUtil::kIgnoreNull | JsonUtil::kQuoteString);
	return ss.str();
}


CDataStore::CDataStore()
{
}

CDataStore::~CDataStore()
{
}

bool CDataStore::open(const std::string& path)
{
	comn::AutoCritSec lock(m_cs);
	return m_store.open(path);
}

void CDataStore::close()
{
	comn::AutoCritSec lock(m_cs);
	m_store.close();
}

bool CDataStore::isOpen()
{
	comn::AutoCritSec lock(m_cs);
	return m_store.isOpen();
}

bool CDataStore::addEvent(EventRecord& record)
{
	std::ostringstream oss;
	oss << "insert into event_record values(null";
	oss << "," << record.type;
	oss << ",'" << record.company << "'";
	oss << ",'" << record.station << "'";
	oss << "," << record.num;
	oss << "," << record.capacity;
	oss << "," << record.peoples;
	oss << "," << record.status;
	oss << ",'" << record.memo << "'";
	oss << "," << record.store;
	oss << ",'" << record.filepath << "'";
	oss << "," << record.created;

	oss << ")";

	std::string sql = oss.str();
	return insert(sql, record.id);
}

bool CDataStore::removeEvent(int id)
{
    std::string sql = comn::StringUtil::format("delete from event_record where id='%d'", id);
    return execute(sql);
}

bool CDataStore::getEvent(int id, EventRecord& record)
{
	std::ostringstream oss;
	oss << "select * from event_record where id=" << id;
	std::string sql = oss.str();
	return queryObject(sql, record);
}

bool CDataStore::updateEvent(EventRecord& record)
{
	std::ostringstream oss;
	oss << "replace into event_record values(";
	oss << record.id;
	oss << "," << record.type;
	oss << ",'" << record.company << "'";
	oss << ",'" << record.station << "'";
	oss << "," << record.num;
	oss << "," << record.capacity;
	oss << "," << record.peoples;
	oss << "," << record.status;
	oss << ",'" << record.memo << "'";
	oss << "," << record.store;
	oss << ",'" << record.filepath << "'";
	oss << "," << record.created;

	oss << ")";

	std::string sql = oss.str();
	return update(sql) > 0;
}

bool CDataStore::addImage(ImageRecord& record)
{
	std::ostringstream oss;
	oss << "insert into image_record values(null";
	oss << ",'" << record.company << "'";
	oss << ",'" << record.station << "'";
	oss << "," << record.num;
	oss << "," << record.store;
	oss << ",'" << record.filepath << "'";
	oss << "," << record.created;

	oss << ")";

	std::string sql = oss.str();
	return insert(sql, record.id);
}

bool CDataStore::removeImage(int id)
{
    std::string sql = comn::StringUtil::format("delete from image_record where id='%d'", id);
    return execute(sql);
}

bool CDataStore::getImage(int id, ImageRecord& record)
{
	std::ostringstream oss;
	oss << "select * from image_record where id=" << id;
	std::string sql = oss.str();
	return queryObject(sql, record);
}

bool CDataStore::findEvent(const char* filepath, EventRecord& record)
{
	if (filepath == NULL)
	{
		return false;
	}

	std::ostringstream oss;
	oss << "select * from event_record where id=" << "'" << filepath << "'";
	std::string sql = oss.str();
	return queryObject(sql, record);
}

bool CDataStore::findImage(const char* filepath, ImageRecord& record)
{
	if (filepath == NULL)
	{
		return false;
	}

	std::ostringstream oss;
	oss << "select * from image_record where id=" << "'" << filepath << "'";
	std::string sql = oss.str();
	return queryObject(sql, record);
}


int CDataStore::searchEvent(const char* company, const char* station, int num, time_t fromTime, time_t toTime, EventRecord* records, int size)
{
	std::ostringstream oss;
	oss << "select * from event_record ";

	comn::StringJoiner joiner(" where ", " and ");
	if (!isEmpty(company))
	{
		std::string condition = comn::StringUtil::format("company like '%%%s%%'", company);
		joiner.add(condition);
	}

	if (!isEmpty(station))
	{
		std::string condition = comn::StringUtil::format("station like '%%%s%%'", station);
		joiner.add(condition);
	}

	if (num > 0)
	{
		joiner.add("num = ", num);
	}

	if (fromTime != 0)
	{
		joiner.add("created >= ", fromTime);
	}

	if (toTime != 0)
	{
		joiner.add("created < ", toTime);
	}

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	oss << " order by created ";
	oss << " limit " << size;

	std::string sql = oss.str();
	return queryObjects(sql, records, size);
}

bool CDataStore::removeEvents(const char* company, const char* station, int num, time_t fromTime, time_t toTime)
{
	std::ostringstream oss;
	oss << "delete event_record ";

	comn::StringJoiner joiner(" where ", " and ");
	if (!isEmpty(company))
	{
		std::string condition = comn::StringUtil::format("company like '%%%s%%'", company);
		joiner.add(condition);
	}

	if (!isEmpty(station))
	{
		std::string condition = comn::StringUtil::format("station like '%%%s%%'", station);
		joiner.add(condition);
	}

	if (num > 0)
	{
		joiner.add("num = ", num);
	}

	if (fromTime != 0)
	{
		joiner.add("start_time >= ", fromTime);
	}

	if (toTime != 0)
	{
		joiner.add("start_time < ", toTime);
	}

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	std::string sql = oss.str();
	return execute(sql);
}



int CDataStore::searchImage(const char* company, const char* station, int num, time_t fromTime, time_t toTime, ImageRecord* records, int size)
{
	std::ostringstream oss;
	oss << "select * from image_record ";

	comn::StringJoiner joiner(" where ", " and ");
	if (!isEmpty(company))
	{
		std::string condition = comn::StringUtil::format("company like '%%%s%%'", company);
		joiner.add(condition);
	}

	if (!isEmpty(station))
	{
		std::string condition = comn::StringUtil::format("station like '%%%s%%'", station);
		joiner.add(condition);
	}

	if (num > 0)
	{
		joiner.add("num = ", num);
	}

	if (fromTime != 0)
	{
		joiner.add("created >= ", fromTime);
	}

	if (toTime != 0)
	{
		joiner.add("created < ", toTime);
	}

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	oss << " order by created ";
	oss << " limit " << size;

	std::string sql = oss.str();
	return queryObjects(sql, records, size);
}

bool CDataStore::removeImages(const char* company, const char* station, int num, time_t fromTime, time_t toTime)
{
	std::ostringstream oss;
	oss << "delete image_record ";

	comn::StringJoiner joiner(" where ", " and ");
	if (!isEmpty(company))
	{
		std::string condition = comn::StringUtil::format("company like '%%%s%%'", company);
		joiner.add(condition);
	}

	if (!isEmpty(station))
	{
		std::string condition = comn::StringUtil::format("station like '%%%s%%'", station);
		joiner.add(condition);
	}

	if (num > 0)
	{
		joiner.add("num = ", num);
	}

	if (fromTime != 0)
	{
		joiner.add("start_time >= ", fromTime);
	}

	if (toTime != 0)
	{
		joiner.add("start_time < ", toTime);
	}

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	std::string sql = oss.str();
	return execute(sql);
}


bool CDataStore::execute(const std::string& sql)
{
	comn::AutoCritSec lock(m_cs);
	return m_store.execute(sql);
}

bool CDataStore::query(const std::string& sql, std::string& value)
{
	comn::AutoCritSec lock(m_cs);
	return m_store.query(sql, value);
}

bool CDataStore::query(const std::string& sql, int& value)
{
	comn::AutoCritSec lock(m_cs);
	return m_store.query(sql, value);
}

int CDataStore::update(const std::string& sql)
{
	comn::AutoCritSec lock(m_cs);
	return m_store.update(sql);
}

bool CDataStore::query(const std::string& sql, Json::Value& json)
{
	return queryObject(sql, json);
}

bool CDataStore::query(const std::string& sql, JsonList& jsonList)
{
	int count = queryObjects(sql, jsonList);
	return true;
}

bool CDataStore::queryToJson(const std::string& sql, std::string& text)
{
	Json::Value json;
	if (!query(sql, json))
	{
		return false;
	}

	text = json.toStyledString();
	return true;
}

bool CDataStore::querySomeToJson(const std::string& sql, std::string& text)
{
	JsonList jsonList;
	if (!query(sql, jsonList))
	{
		return false;
	}

	text = JsonUtil::toString(jsonList);
	return true;
}



bool CDataStore::update(const std::string& table, const Json::Value& json, const std::string& condition)
{
	std::string sql = makeUpdate(table, json);
	sql += " ";
	sql += condition;
	return update(sql) > 0;
}

bool CDataStore::insert(const std::string& sql, const std::string& table, long& id)
{
	int64_t lid = 0;
	if (!insert(sql, lid))
	{
		return false;
	}

	id = (long)lid;
	return true;
}

bool CDataStore::insert(const std::string& table, const Json::Value& json)
{
	std::string sql = makeInsert(table, "INSERT INTO", json);
	return execute(sql);
}

bool CDataStore::insert(const std::string& table, const Json::Value& json, long& id)
{
	std::string sql = makeInsert(table, "INSERT INTO", json);
	return insert(sql, table, id);
}

bool CDataStore::replace(const std::string& table, const Json::Value& json)
{
	std::string sql = makeInsert(table, "REPLACE INTO", json);
	return execute(sql);
}

bool CDataStore::search(const std::string& table, const std::string& query,
		const std::string& condition, const std::string& order,
		int offset, int limit, int& total, JsonList& jsonList)
{
	std::string countSql = comn::StringUtil::format("select count(*) from %s", table.c_str());
	if (condition.size())
	{
		countSql += ' ';

		if (!comn::StringUtil::startsWith(condition, "where"))
		{
			countSql += "where ";
		}

		countSql += condition;
	}

	if (!this->query(countSql, total))
	{
		return false;
	}

	std::string sql = query;

	if (condition.size())
	{
		sql += ' ';

		if (!comn::StringUtil::startsWith(condition, "where"))
		{
			sql += "where ";
		}

		sql += condition;
	}

	if (order.size() > 0)
	{
		sql += ' ';

		if (!comn::StringUtil::startsWith(order, "order"))
		{
			sql += "order by ";
		}

		sql += order;
	}

	sql += comn::StringUtil::format(" limit %d,%d ", offset, limit);
	return this->query(sql, jsonList);
}






int CDataStore::queryInt(const std::string& sql, int defValue)
{
	comn::AutoCritSec lock(m_cs);
    return m_store.queryInt(sql, defValue);
}

bool CDataStore::insert(const std::string& sql, int& autoId)
{
	comn::AutoCritSec lock(m_cs);
	return m_store.insert(sql, autoId);
}

bool CDataStore::insert(const std::string& sql, int64_t& autoId)
{
	comn::AutoCritSec lock(m_cs);
	return m_store.insert(sql, autoId);
}
